package com.scm.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.scm.model.Vender;
import com.scm.util.DbUtil;
import com.scm.util.StringUtil;

public class VenderDao extends BaseDao<Vender>{
	
	public VenderDao() {
		super();
	}

	public VenderDao(Connection conn) {
		super();
		this.conn = conn;
	}
	public void del(String vendercode) throws SQLException {
		conn=null;
		PreparedStatement st = null;
		try {
			conn = DbUtil.getConn();
			st = conn.prepareStatement("delete from vender where vendercode=?");
			st.setString(1, vendercode);
			st.execute();
		}finally {
			DbUtil.close(conn, null, st);
		}
	}
	public void add(Vender vender) throws SQLException {
		conn=null;
		PreparedStatement st = null;
		try {
			conn = DbUtil.getConn();
			String sql = "insert into vender values(?,?,?,?,?,?,?,?,?)";
			st = conn.prepareStatement(sql);
			st.setString(1, vender.getVenderCode());
			st.setString(2, vender.getName());
			st.setString(3, vender.getPassword());
			st.setString(4, vender.getContactor());
			st.setString(5, vender.getAddress());
			st.setString(6, vender.getPostCode());
			st.setString(7, vender.getTel());
			st.setString(8, vender.getFax());
			st.setString(9, vender.getCreateDate());
			st.executeUpdate();
		} finally {
			DbUtil.close(conn, null, st);
		}
	}
	public int query(String vendercode) throws SQLException {
		conn=null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			conn = DbUtil.getConn();
			st = conn.prepareStatement("select * from vender where vendercode=?");
			st.setString(1, vendercode);
			rs = st.executeQuery();
			while (rs.next()) {
				return 1;
			}
			return -1;
		} finally {
			DbUtil.close(conn, rs, st);
		}
	}
	public List<Vender> queryAll() throws SQLException {
		conn=null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			conn = DbUtil.getConn();
			st = conn.prepareStatement("select * from vender");
			rs = st.executeQuery();
			List<Vender> venders = new ArrayList<Vender>();
			while (rs.next()) {
				Vender vender = new Vender();
				vender.setVenderCode(rs.getString("vendercode"));
				vender.setName(rs.getString("name"));
				vender.setPassword(rs.getString("password"));
				vender.setContactor(rs.getString("contactor"));
				vender.setAddress(rs.getString("address"));
				vender.setPostCode(rs.getString("postcode"));
				vender.setTel(rs.getString("tel"));
				vender.setFax(rs.getString("fax"));
				vender.setCreateDate(rs.getString("createdate"));
				venders.add(vender);
			}
			return venders;
		} finally {
			DbUtil.close(conn, rs, st);
		}
	}
	public int change(Vender vender) throws SQLException {
		conn=null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			conn = DbUtil.getConn();
			st = conn.prepareStatement("update vender set name=?,password=?,contactor=?,address=?,postcode=?,tel=?,fax=?,createdate=? where vendercode=?");
			st.setString(1, vender.getName());
			st.setString(2, vender.getPassword());
			st.setString(3, vender.getContactor());
			st.setString(4, vender.getAddress());
			st.setString(5, vender.getPostCode());
			st.setString(6, vender.getTel());
			st.setString(7, vender.getFax());
			st.setString(8, vender.getCreateDate());
			st.setString(9, vender.getVenderCode());
			return st.executeUpdate();
		} finally {
			DbUtil.close(conn, rs, st);
		}
	}
	public Vender login(String vendercode,String password) throws SQLException {
		conn=null;
		PreparedStatement st = null;
		ResultSet rs = null;
		Vender vender = null;
		try {
			conn = DbUtil.getConn();
			st = conn.prepareStatement("select * from vender where vendercode=? and password=?");
			st.setString(1, vendercode);
			st.setString(2, password);
			rs = st.executeQuery();
			if(rs.next()) {
				vender = new Vender();
				vender.setPostCode(rs.getString("postcode"));
				vender.setName(rs.getString("name"));
				vender.setPassword(password);
				vender.setVenderCode(vendercode);
				vender.setContactor(rs.getString("contactor"));
				vender.setAddress(rs.getString("address"));
				vender.setCreateDate(rs.getString("createdate"));
				vender.setFax(rs.getString("fax"));
				vender.setTel(rs.getString("tel"));
			}
		} finally {
			DbUtil.close(conn, rs, st);
		}
	return vender;
	}
	//
	public List<Vender> selectData(Map<String,String> querymap) throws SQLException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Vender> venders = new ArrayList<Vender>();
		try {
			StringBuilder sql = new StringBuilder("select * ");
			Map<Integer, String> params = new HashMap<Integer, String>();
			int index = generateQueryCondition(querymap, params, sql);
			sql.append(" limit ?,?");
			ps = conn.prepareStatement(sql.toString());
			for (Integer i : params.keySet()) {
				ps.setObject(i, params.get(i));
			}
			ps.setInt(index++, Integer.parseInt(querymap.get("startNo")));
			ps.setInt(index++, Integer.parseInt(querymap.get("pageSize")));
			rs = ps.executeQuery();
			while (rs.next()) {
				Vender vender = new Vender();
				vender.setVenderCode(rs.getString("vendercode"));
				vender.setName(rs.getString("name"));
				vender.setPassword(rs.getString("password"));
				vender.setContactor(rs.getString("contactor"));
				vender.setAddress(rs.getString("address"));
				vender.setPostCode(rs.getString("postcode"));
				vender.setTel(rs.getString("tel"));
				vender.setFax(rs.getString("fax"));
				vender.setCreateDate(rs.getString("createdate"));
				venders.add(vender);
			}
			return venders;
		} finally {
			DbUtil.close(conn, rs, ps);
		}
	}
	public int generateQueryCondition(Map<String,String> querymap,final Map<Integer,String> params,final StringBuilder sql) {
		sql.append("from vender where 1=1");
		int index = 1;
		String vendercode = querymap.get("vendercode");
		if(!StringUtil.isEmpty(vendercode)) {
			sql.append(" and vendercode like concat('%',?,'%')");
			params.put(index++, vendercode);
		}
		String name = querymap.get("name");
		if(!StringUtil.isEmpty(name)) {
			sql.append(" and name like concat('%',?,'%')");
			params.put(index++,name);
		}
		String password = querymap.get("password");
		if(!StringUtil.isEmpty(password)) {
			sql.append(" and password = ?");
			params.put(index++, password);
		}
		String contactor = querymap.get("contator");
		if(!StringUtil.isEmpty(contactor)) {
			sql.append(" and contactor = ?");
			params.put(index++, contactor);
		}
		String address = querymap.get("address");
		if(!StringUtil.isEmpty(address)) {
			sql.append(" and address = ?");
			params.put(index++, address);
		}
		String postcode = querymap.get("postcode");
		if(!StringUtil.isEmpty(postcode)) {
			sql.append(" and postcode = ?");
			params.put(index++, postcode);
		}
		String tel = querymap.get("tel");
		if(!StringUtil.isEmpty(tel)) {
			sql.append(" and tel = ?");
			params.put(index++, tel);
		}
		String fax = querymap.get("fax");
		if(!StringUtil.isEmpty(fax)) {
			sql.append(" and fax = ?");
			params.put(index++, fax);
		}

		String startday = querymap.get("startday");
		if(!StringUtil.isEmpty(startday)) {
			startday = startday+" 00:00:00";
			sql.append(" and createdate >= ?");
			params.put(index++, startday);
		}
		String endday = querymap.get("endday");
		if(!StringUtil.isEmpty(endday)) {
			endday = endday+" 23:59:59";
			sql.append(" and createdate <= ?");
			params.put(index++, endday);
		}
		return index;
	}
}
